package com.hotel.repository;

import com.hotel.config.DatabaseConfig;
import com.hotel.entity.Room;
import org.springframework.stereotype.Repository; // 【重要导入】

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

@Repository // 【重要修改】添加此注解，让 Spring 扫描并管理这个 Bean
public class RoomRepository {
    public RoomRepository() {}

    public void save(Room room) {
        // 【重要修改】在 SQL 插入语句中添加 STATUS 字段，并设置其值。
        // 你的数据库截图显示有 status 字段，而你的插入 SQL 缺少它。
        String sql = "INSERT INTO Rooms (ROOM_NUMBER, ROOM_TYPE, BEDS, PRICE, STATUS) values(?,?,?,?,?)";
        try (Connection conn = DriverManager.getConnection(
                DatabaseConfig.URL,
                DatabaseConfig.USER,
                DatabaseConfig.PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            stmt.setString(1, room.getRoom_number());
            stmt.setString(2, room.getRoom_type());
            stmt.setInt(3, room.getBeds());
            stmt.setBigDecimal(4, room.getPrice());
            stmt.setString(5, room.getStatus()); // 【重要修改】设置 STATUS 字段的值
            stmt.executeUpdate();

            try (ResultSet rs = stmt.getGeneratedKeys()) {
                if (rs.next()) {
                    room.setRoom_id(rs.getInt(1));
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("保存房间失败", e);
        }
    }

    public List<Room> findAll() {
        String sql = "SELECT * FROM Rooms";
        List<Room> rooms = new ArrayList<>();
        try (Connection conn = DriverManager.getConnection(
                DatabaseConfig.URL,
                DatabaseConfig.USER,
                DatabaseConfig.PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql);
             ResultSet rs = stmt.executeQuery()){

            while (rs.next()) {
                rooms.add(mapResultSetToRoom(rs));
            }
            return rooms;
        } catch (SQLException e) {
            throw new RuntimeException("查询房间失败", e);
        }
    }


    public void delete(Integer roomId) {
        String sql = "DELETE FROM Rooms WHERE ROOM_ID =?";
        try (Connection conn = DriverManager.getConnection(
                DatabaseConfig.URL,
                DatabaseConfig.USER,
                DatabaseConfig.PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, roomId);
            int affectedRows = stmt.executeUpdate(); // 检查受影响的行数
            if (affectedRows == 0) {
                throw new RuntimeException("删除房间失败: 房间ID " + roomId + " 不存在。");
            }
        } catch (SQLException e) {
            throw new RuntimeException("删除房间失败", e);
        }
    }

    public void update(Room room) {
        String sql = "UPDATE Rooms SET ROOM_NUMBER =?, ROOM_TYPE =?, BEDS =?, PRICE =?, STATUS =? WHERE ROOM_ID =?";
        try (Connection conn = DriverManager.getConnection(
                DatabaseConfig.URL,
                DatabaseConfig.USER,
                DatabaseConfig.PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setString(1, room.getRoom_number());
            stmt.setString(2, room.getRoom_type());
            stmt.setInt(3, room.getBeds());
            stmt.setBigDecimal(4, room.getPrice());
            stmt.setString(5, room.getStatus());
            stmt.setInt(6, room.getRoom_id());

            int count = stmt.executeUpdate();
            if (count == 0) {
                throw new RuntimeException("更新房间失败: 房间ID " + room.getRoom_id() + " 不存在。");
            }
        }catch (SQLException e) {
            throw new RuntimeException("更新房间失败", e);
        }
    }

    public void updateStatus(Integer roomId, String status) {
        String sql = "UPDATE Rooms SET STATUS =? WHERE ROOM_ID =?";
        try (Connection conn = DriverManager.getConnection(
                DatabaseConfig.URL,
                DatabaseConfig.USER,
                DatabaseConfig.PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, status);
            stmt.setInt(2, roomId);
            int affectedRows = stmt.executeUpdate(); // 检查受影响的行数
            if (affectedRows == 0) {
                throw new RuntimeException("更新房间状态失败: 房间ID " + roomId + " 不存在。");
            }
        } catch (SQLException e) {
            throw new RuntimeException("更新房间状态失败", e);
        }
    }

    public List<Room> findAvailableRooms() {
        String sql = "SELECT * FROM Rooms WHERE  STATUS = '空闲'";
        List<Room> rooms = new ArrayList<>();
        try (Connection conn = DriverManager.getConnection(
                DatabaseConfig.URL,
                DatabaseConfig.USER,
                DatabaseConfig.PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    rooms.add(mapResultSetToRoom(rs));
                }
            }
            return rooms;
        } catch (SQLException e) {
            throw new RuntimeException("查询房间失败", e);
        }
    }

    // 【重要修改】移除 static 关键字。让它成为实例方法。
    public Optional<Room> findById(Integer roomId) {
        String sql = "SELECT * FROM Rooms WHERE ROOM_ID = ?";
        try (Connection conn = DriverManager.getConnection(
                DatabaseConfig.URL,
                DatabaseConfig.USER,
                DatabaseConfig.PASSWORD);
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, roomId);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return Optional.of(mapResultSetToRoom(rs));
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException("根据ID查询房间失败", e);
        }
        return Optional.empty(); // 如果没有找到，返回 Optional.empty()
    }
    // 【重要修改】移除 static 关键字。让它成为实例方法。
    private Room mapResultSetToRoom(ResultSet rs) throws SQLException {
        Room room = new Room();
        room.setRoom_id(rs.getInt("room_id"));
        room.setRoom_number(rs.getString("room_number"));
        room.setRoom_type(rs.getString("room_type"));
        room.setBeds(rs.getInt("beds"));
        room.setPrice(rs.getBigDecimal("price"));
        room.setStatus(rs.getString("status"));
        return room;
    }
}